﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for CauHoi
/// </summary>
public class CauHoiDAL
{
    public static object IsNull(object checkValue, object replaceValue)
    {
        if (checkValue.Equals(System.DBNull.Value) || checkValue == null)
            return replaceValue;
        return checkValue;
    }

    public static List<CauHoiINFO> listAll()
    {
        string sql = "select * from CauHoi";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetListObjects<CauHoiINFO>(dr);
    }

    public static CauHoiINFO GetInfo(int macauhoi)
    {
        string sql = "select * from CauHoi where MaCH =" + macauhoi + "";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetObject<CauHoiINFO>(dr);
    }

    public static int Insert(SqlTransaction trans, CauHoiINFO obj)
    {
        string sql = "INSERT INTO CAUHOI (MaCH,MaDoKho,NoiDungCH,NgayTao,MaLoai,MaMon,SoLuaChon) VALUES (" + obj.MaCH + ","+obj.MaDoKho+",N'" + obj.NoiDungCH + "',getdate()," + obj.MaLoai + "," + obj.MaMon + "," + obj.SoLuaChon + ")";
        return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
    }

    public static int Update(SqlTransaction trans, CauHoiINFO obj)
    {
        string sql = "Update CAUHOI set NoiDungCH=N'" + obj.NoiDungCH + "', MaLoai = "+obj.MaLoai+", MaMon = "+obj.MaMon+", SoLuaChon = "+obj.SoLuaChon+" Where MaCH=" + obj.MaCH + "";
        return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
    }

    public static int Delete(SqlTransaction trans, int macauhoi)
    {
        string sql = "Delete from CauHoi where MaCH =" + macauhoi + "";
        return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
    }

    public static int GetMa()
    {
        string sql = "SELECT ISNULL(MAX(MaCH),0) FROM CAUHOI";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        int ma = 0;
        if (dr.Read())
        {
            ma = int.Parse(dr[0].ToString());
        }
        dr.Close();
        return ma;
    }

    public static DataTable danhsach()
    {
        string sql = "SELECT CAUHOI.*, MONTHI.TenMon,LOAICAUHOI.TenLoai FROM CAUHOI INNER JOIN ";
        sql+=" MONTHI ON CAUHOI.MaMon = MONTHI.MaMon INNER JOIN ";
        sql +=" LOAICAUHOI ON CAUHOI.MaLoai = LOAICAUHOI.MaLoai";
        return SQLHelper.ExecuteDataTable(Conn.ConnectionString, sql, CommandType.Text);
    }

    public static int ThemCauHoi(CauHoiINFO mCauHoi, List<DapAnINFO> dsDapAn)
    {
        SqlConnection conns = null;
        SqlTransaction trans = null;
        int macauhoi = 0;
        try
        {
            macauhoi = GetMa();
            macauhoi++;
            conns = new SqlConnection(Conn.ConnectionString);
            if (conns.State != ConnectionState.Open)
                conns.Open();
            trans = conns.BeginTransaction();
            mCauHoi.MaCH = macauhoi;
            if (Insert(trans,mCauHoi) <= 0)
            {
                trans.Rollback();
                conns.Close();
                return 0;
            }
            for (int i = 0; i < dsDapAn.Count; i++ )
            {
                dsDapAn[i].MaDA = i+1;
                dsDapAn[i].MaCH = macauhoi;

                if (DapAnDAL.Insert(trans, dsDapAn[i]) <= 0)
                {
                    trans.Rollback();
                    conns.Close();
                    return 0;
                }
            }
            
            trans.Commit();
            conns.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conns.Close();
            return 0;
        }
    }

    public static int CapNhatCauHoi(CauHoiINFO mCauHoi, List<DapAnINFO> dsDapAn)
    {
        SqlConnection conns = null;
        SqlTransaction trans = null;
        try
        {
            conns = new SqlConnection(Conn.ConnectionString);
            if (conns.State != ConnectionState.Open)
                conns.Open();
            trans = conns.BeginTransaction();
            if (Update(trans, mCauHoi) <= 0)
            {
                trans.Rollback();
                conns.Close();
                return 0;
            }
            if (DapAnDAL.Delete(trans, mCauHoi.MaCH) <= 0)
            {
                trans.Rollback();
                conns.Close();
                return 0;
            }
            for (int i = 0; i < dsDapAn.Count; i++)
            {
                dsDapAn[i].MaDA = i + 1;
                dsDapAn[i].MaCH = mCauHoi.MaCH;

                if (DapAnDAL.Insert(trans, dsDapAn[i]) <= 0)
                {
                    trans.Rollback();
                    conns.Close();
                    return 0;
                }
            }

            trans.Commit();
            conns.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conns.Close();
            return 0;
        }
    }

    public static int XoaCauHoi(int MaCauHoi)
    {
        SqlConnection conns = null;
        SqlTransaction trans = null;
        try
        {
            conns = new SqlConnection(Conn.ConnectionString);
            if (conns.State != ConnectionState.Open)
                conns.Open();
            trans = conns.BeginTransaction();
            if (Delete(trans, MaCauHoi) <= 0)
            {
                trans.Rollback();
                conns.Close();
                return 0;
            }
            if (DapAnDAL.Delete(trans, MaCauHoi) <= 0)
            {
                trans.Rollback();
                conns.Close();
                return 0;
            }
            trans.Commit();
            conns.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conns.Close();
            return 0;
        }
    }

    public static DataTable timkiem(int maloai, int mamon, int dokho)
    {
        string sql = "SELECT CAUHOI.*, MONTHI.TenMon,LOAICAUHOI.TenLoai FROM CAUHOI INNER JOIN ";
        sql += " MONTHI ON CAUHOI.MaMon = MONTHI.MaMon INNER JOIN ";
        sql += " LOAICAUHOI ON CAUHOI.MaLoai = LOAICAUHOI.MaLoai Where 1=1";
        if(maloai > 0 )
           sql += " AND CAUHOI.MaLoai = " + maloai;
        if (mamon > 0)
            sql += " AND CAUHOI.MaMon = " + mamon;
        if (dokho > 0)
            sql += " AND CAUHOI.MaDoKho = " + dokho;
        return SQLHelper.ExecuteDataTable(Conn.ConnectionString, sql, CommandType.Text);
    }

    public static List<CauHoiINFO> RandCauHoi(int SoCauHoi,int MaMon, int MaDoKho)
    {
        string sql = "SELECT TOP " + SoCauHoi + " * FROM CAUHOI Where MaMon = " + MaMon + " AND MaDoKho=" + MaDoKho + " ORDER BY NEWID()";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetListObjects<CauHoiINFO>(dr);
    }

    public static int TongSoCauHoi(int MaMon, int MaDoKho)
    {
        string sql = "SELECT Count(MaCH) FROM CAUHOI Where MaMon = " + MaMon + " AND MaDoKho = "+ MaDoKho;
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        int count = 0;
        if (dr.Read())
        {
            count = int.Parse(dr[0].ToString());
        }
        dr.Close();
        return count;
    }
}